# -*- coding: utf-8 -*-


import sqlite3

conn = sqlite3.connect('test.db')
print ("数据库打开成功")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("数据表创建成功")
conn.commit()

data = {
    "ID": 1,
    "NAME": "张三",
    "AGE": 20,
    "ADDRESS": "北京",
    "SALARY": 10000.00
}

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
conn.commit()

cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()

c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
conn.close()




from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()
class User(base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(20))
    age = Column(Integer)
    def get_json(self):
        return {
            'id': self.id,
            'name': self.name,
            'age': self.age
        }


engine = create_engine('sqlite:////home/jiangzx/project/data-crawler-101/code/test2.db')
DBsession = sessionmaker(bind=engine)

base.metadata.create_all(engine)



session = DBsession()

#增
new_user = User(name='test2',age=10)
session.add(new_user)
#删
session.query(User).filter(User.id == 2).delete()
#改
session.query(User).filter(User.id == 3).update({User.name: "update_test"})
session.commit()
#查
people = session.query(User).all()
for item in people:
    print(item.get_json())
session.close()